package connectivity;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import main.WinkelApplication;
import model.Category;
import model.Customer;
import model.Order;
import model.OrderLine;
import model.Product;

public class QueryManager {

    private final Dbmanager dbmanager;

    public QueryManager(Dbmanager dbmanager) {
        this.dbmanager = dbmanager;
    }

    
    public List<String> getPaymentMethods() {
        List<String> paymentmethods = new ArrayList<String>();
        
        try {
            String sql = "SELECT * FROM paymentmethods";
            ResultSet result = dbmanager.doQuery(sql);
            
            while(result.next()) {
                paymentmethods.add(result.getString("name"));
            }
        } catch(SQLException e) {
            WinkelApplication.msgbox("Fout bij het ophalen van paymentmenthods:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        
        return paymentmethods;
    }
    
    public Category getCategory(int categoryId) {
        Category category = new Category();
        try {
            String sql = "SELECT * FROM category WHERE category_id='" + categoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                category = new Category(result.getInt("category_id"),
                        result.getString("name"),
                        result.getString("description"));
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Kon de categorienaam niet verkrijgen:\n" + Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return category;
    }    
    
   public void newCategory(Category category) {
       try {    
            String sql = "INSERT INTO `category` (name, description)"
                    + " VALUES ('"+category.getName()+"', '"+category.getDescription()+"');";
            //dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens toevoegen van categorie: "+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    } 


   public void changeCategory(Category category) {
       try {    
    
            String sql = "UPDATE `category` SET `name`='"+category.getName()+"',"
                    + "`description`='"+category.getDescription()
                    + " WHERE `product_id`='"+category.getCategoryId()+"';";
            //dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens aanpassen van categorie: "+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }       
    
   public void deleteCategory(int categoryId) {
       try {    
            String sql = "DELETE FROM category WHERE category_id='" + categoryId + "'";
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens het verwijderen van categorie "+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }      
 
    public String getCategoryName(int categoryId) {
        String categoryName = "";
        try {
            String sql = "SELECT name FROM category WHERE category_id='" + categoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                categoryName = result.getString("name");
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Kon de categorienaam niet verkrijgen:\n" + Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categoryName;
    }

    public List<Category> getCategories() {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM category ORDER BY category_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Category category = new Category(result.getInt("category_id"),
                        result.getString("name"),
                        result.getString("description"));
                categories.add(category);
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Kon categorieen niet uit database halen:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categories;
    }
       
    
    
    //Added by: Bryan Slop
    //Function: Search for customer(klant) and give back an arraylist, in case more customers are found.
    public List<Customer> searchCustomer(String search) {
        List<Customer> Customers = new ArrayList<Customer>();
        try {
            String sql = "SELECT * FROM customer WHERE name LIKE '%" + search + "%'";
            ResultSet result = dbmanager.doQuery(sql);
            
            while(result.next()) {
                Customers.add(new Customer(result.getInt("customer_id"),
                        result.getString("name"),
                        result.getString("address"),
                        result.getString("zipcode"),
                        result.getString("city")));
            }
        } catch(SQLException e) {
            WinkelApplication.msgbox("Het zoeken van een klant is mislukt, foutmelding:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        
        return Customers;
    }
    

    
    public Product getProduct(int productId) {
        Product product = new Product();
        try {
            String sql = "SELECT * FROM product WHERE product_id='" + productId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                product = new Product(result.getInt("product_id"),
                        result.getInt("category_id"),
                        result.getString("name"),
                        result.getString("description"),
                        result.getDouble("price"),
                        result.getInt("stock"),
                        result.getInt("min_stock"));
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout bij ophalen van product op ID:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return product;
    }

    public List<Product> getProducts(int categoryId) {
        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT * FROM product WHERE category_id='" + categoryId + "' ORDER BY name ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(result.getInt("product_id"),
                        result.getInt("category_id"),
                        result.getString("name"),
                        result.getString("description"),
                        result.getDouble("price"),
                        result.getInt("stock"),
                        result.getInt("min_stock")));
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens inladen van lijst met producten in categorie:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
    public Order getOrder(int orderId) {
        Order order = new Order();
        
        try {
            String sql = "SELECT * FROM `order` WHERE order_id='" + orderId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if(result.next()) {
                String customer = getCustomer(result.getInt("customer_id")).getName();
                order = new Order(result.getInt("order_id"),
                        result.getInt("customer_id"),
                        customer,
                        result.getString("note"),
                        result.getString("paymentmethod"),
                        result.getString("date"),
                        result.getInt("status"));
            }
        } catch(SQLException e) {
            WinkelApplication.msgbox("Fout tijdens het ophalen van een order:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
            System.out.println("Fout tijdens het ophalen van een order:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        
        return order;
    }
    
    public List<Order> getOrders(int customerID) {
        List<Order> orders = new ArrayList<Order>();
        String sql;
        try {
            if(customerID == -1) sql = "SELECT * FROM `order` ORDER BY order_id ASC";
            else sql = "SELECT * FROM `order` WHERE customer_id='" + customerID + "' ORDER BY order_id ASC";
            
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                String customer = getCustomer(result.getInt("customer_id")).getName();
                
                orders.add(new Order(result.getInt("order_id"),
                        result.getInt("customer_id"),
                        customer,
                        result.getString("note"),
                        result.getString("paymentmethod"),
                        result.getString("date"),
                        result.getInt("status")));
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens laden van 'order'-lijst:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
            System.out.println("Fout tijdens laden van 'order'-lijst:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return orders;
    }
    
    public List<OrderLine> getOrderLines(int orderId) {
        List<OrderLine> orderLines = new ArrayList<OrderLine>();
        try {
            String sql = "SELECT * FROM `orderline` WHERE order_id='" + orderId + "' ORDER BY orderline_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                String product = getProduct(result.getInt("product_id")).getName();
                
                orderLines.add(new OrderLine(result.getInt("orderline_id"),
                        result.getInt("order_id"),
                        result.getInt("product_id"),
                        product,
                        result.getInt("amount")));
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens laden van 'order'-lijst:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
            System.out.println("Fout tijdens laden van 'order'-lijst:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return orderLines;
    }
    
    public List<Product> getAllProducts() {
        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT product.*, category.name AS \"category\" FROM product, category WHERE product.category_id = category.category_id;";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(result.getInt("product_id"),
                        result.getInt("category_id"),
                        result.getString("name"),
                        result.getString("description"),
                        result.getDouble("price"),
                        result.getInt("stock"),
                        result.getInt("min_stock"),
                        result.getString("category")));
                
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens inladen van alle producten"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
   public void deleteProduct(int productId) {
       try {    
            String sql = "DELETE FROM product WHERE product_id='" + productId + "'";
            //dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
       } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens inladen van alle producten: "+Dbmanager.SQL_EXCEPTION + e.getMessage());
       }
   }      

      public List<Product> getMinProducts() {
        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT product.*, category.name AS \"category\" FROM product, category WHERE product.category_id = category.category_id AND product.stock < product.min_stock;";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(result.getInt("product_id"),
                        result.getInt("category_id"),
                        result.getString("name"),
                        result.getString("description"),
                        result.getDouble("price"),
                        result.getInt("stock"),
                        result.getInt("min_stock"),
                        result.getString("category")));
                
            }
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens inladen van alle producten"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
   public void changeProduct(Product product) {
       try {    
    
            String sql = "UPDATE `product` SET `category_id`='"+product.getCategoryId()+"',"
                    + "`name`='"+product.getName()+"',"
                    + "`price`='"+product.getPrice()+"',"
                    + "`description`='"+product.getDescription()+"',"
                    + "`stock`='"+product.getStock()+"',"
                    + "`min_stock`='"+product.getMinStock()+"'"
                    + "WHERE `product_id`='"+product.getProductId()+"';";
            //dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens aanpassen van product: "+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }   
   
   public void alterProductStock(int productId, int changeStock) {
       try {    
    
            String sql = "UPDATE `product` SET stock = stock + "+changeStock+" "
                    + "WHERE `product_id`='"+productId+"';";
            //dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens aanpassen van voorraad van product: "+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }   
   
   public void changeCustomer(Customer customer) {
       try {    
    
            String sql = "UPDATE `customer` SET `customer_id`='"+customer.getCustomerId()+"',"
                    + "`name`='"+customer.getName()+"',"
                    + "`address`='"+customer.getAddress()+"',"
                    + "`zipcode`='"+customer.getZipcode()+"',"
                    + "`city`='"+customer.getCity()+"'"
                    + "WHERE `customer_id`='"+customer.getCustomerId()+"';";
            dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens inladen van alle Gegevens: "+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }        
   
   public void newProduct(Product product) {
       try {    
            String sql = "INSERT INTO `product` (category_id, name, price, description, stock, min_stock)"
                    + " VALUES ('"+product.getCategoryId()+"', '"+product.getName()+"', '"+product.getPrice()+"','"+product.getDescription()+"',"
                    + "'"+product.getStock()+"','"+product.getMinStock()+"');";
            //dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens inladen van alle producten: "+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }    
   
    public void setOrder(model.Basket basket, int customer_id, String note, String paymentmethod) {
        String SQL_order = "INSERT INTO `order` (customer_id, note, paymentmethod, date, status)"
                + " VALUES('" + customer_id + "', '" + note + "', '" + paymentmethod + "', "
                + "CURDATE(), '0')";
        int order_id = 0;
        try {
            ResultSet result = dbmanager.insertQuery(SQL_order);
            result.next();
            order_id = result.getInt(1);
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens opslaan van order:\nconnectivity.QueryManager.setOrder() Exception: " + e.getMessage());
        }
        List<Product> products = basket.getProducts();
        for (Product product : products) {
            int product_id = product.getProductId();
            int amount = basket.getProductAmount(product);
            String SQL_orderProduct = "INSERT INTO orderline (product_id,order_id,amount) VALUES (" + product_id + "," + order_id + "," + amount + ")";
            dbmanager.insertQuery(SQL_orderProduct);
            alterProductStock(product_id, 0-amount);
        }
    }
    
    public List<Customer> getCustomerList() {
        List<Customer> customers = new ArrayList<Customer>();
        
        try {
            String sql = "SELECT * FROM `customer`";
            ResultSet result = dbmanager.doQuery(sql);
            while(result.next()) {
                customers.add(new Customer(result.getInt("customer_id"),
                        result.getString("name"),
                        result.getString("address"),
                        result.getString("zipcode"),
                        result.getString("city")));
            }
        } catch(Exception e) {
            WinkelApplication.msgbox("Fout tijdens laden klantenlijst:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return customers;
    }
    

        
    public Customer getCustomer(int customerId) {
        Customer customer = new Customer();
        try {
            String sql = "SELECT * FROM customer " +
                    "WHERE customer_id='" + customerId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if(result.next()) {
                customer = new Customer(result.getInt("customer_id"),
                        result.getString("name"),
                        result.getString("address"),
                        result.getString("zipcode"),
                        result.getString("city"));
            }
        } catch(SQLException e) {
            WinkelApplication.msgbox("Fout tijdens ophalen van klant op klant ID:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return customer;
    }
    
   public void deleteCustomer(int customerId) {
       try {    
            String sql = "DELETE FROM customer WHERE customer_id='" + customerId + "'";
            //dbmanager.connection.createStatement().executeUpdate(sql);
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch (SQLException e) {
            WinkelApplication.msgbox("Fout tijdens inladen van alle klanten"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }   
    public void addCustomer(Customer customer) {
        try {
            String SQL_klant = "INSERT INTO `customer` (customer_id, name, address, zipcode, city)"
                    + " VALUES('" + customer.getCustomerId() + "', '" + customer.getName() + "', "
                    + "'" + customer.getAddress() + "', '" + customer.getZipcode() + "', "
                    + "'" + customer.getCity() + "')";
            dbmanager.insertQuery(SQL_klant);
        } catch(Exception e) {
            WinkelApplication.msgbox("Fout tijdens toevoegen klant:\n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }            
    }
    
    public void cancelOrder(int orderId, List<OrderLine> orderLines) {
        int product_id;
        int amount;
        for(OrderLine ol: orderLines) {
            product_id = ol.getProductId();
            amount = ol.getAmount();
            alterProductStock(product_id, 0+amount);
        }
    }
    
    public void updateOrderStatus(int orderId, int status) {
        try {
            String sql = "UPDATE `order` SET status='" + status + "' WHERE order_id='" + orderId + "'";
            ResultSet result = dbmanager.insertQuery(sql);
            result.getStatement();
        } catch(SQLException e) {
            WinkelApplication.msgbox("Kon order-status niet veranderen: \n"+Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
    }
}